//
//  SQLiteManager.m
//  TngSQlite
//
//  Created by TNG on 2018/7/26.
//  Copyright © 2018年 Tng. All rights reserved.
//

#import "SQLiteManager.h"
#import "sqlite3.h"
#import "FMDB.h"
#include <sys/stat.h>
#include <dirent.h>

@interface SQLiteManager()
{
    sqlite3 *db;
}

@end

@implementation SQLiteManager

+(instancetype)shareManager
{
    static SQLiteManager * manager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[SQLiteManager alloc] init];
    });
    return manager;
}

-(BOOL)creatTableIsTableviewExist:(NSString *)tableName AndDic:(NSDictionary *)dic AndPrimaryValue:(NSString *)primaryValue
{
    if (![self creatTableIsExist:tableName]) {
        return NO;
    }
    
    FMDatabase *fmdb = [FMDatabase databaseWithPath:[self getTablePahtFromName:tableName]];
    if(![fmdb open]){
        return NO;
    }
    
    NSString * primaryKey = [self getTablePrimaryKey:tableName];
    NSMutableDictionary *dictM = dic.mutableCopy;
    [dictM setValue:primaryValue forKey:primaryKey];

    BOOL result = NO;
    
    NSArray * allKeys = [dic allKeys];
    for (NSString *key in allKeys) {
        if ([self judgeTableIsExistColumn:key AndTable:tableName AndFmdb:fmdb]) {
            continue;
        }
        NSString *sql = [NSString stringWithFormat:@"alter table %@ add %@ TEXT", tableName, key];
        result = [fmdb executeUpdate:sql];
        if (result == NO) {
            break;
        }
    }
    //根据主键查找是否已有数据存在
    NSMutableDictionary * rsDic = [self getObjectWithPrimaryKey:primaryValue tableName:tableName];
    if (rsDic != nil) {
        [self updateObject:dictM tableName:tableName AndprimaryKeyValue:primaryValue];
    }else{
        [self addObject:dictM tableName:tableName AndprimaryKeyValue:primaryValue];
    }
    [fmdb close];
    
    return result;
}

/**
 添加字典类型的数据到tableName表中
 
 @param dic 字典数据
 @param tableName 表名
 @param primaryKeyValue 主键值
 @return 是否成功 添加字典类型的数据到tableName表中
 */
-(BOOL)addObject:(NSDictionary *)dic tableName:(NSString *)tableName AndprimaryKeyValue:(NSString *)primaryKeyValue
{
    NSString *path = [self getTablePahtFromName:tableName];
    FMDatabase *fmdb = [FMDatabase databaseWithPath:path];
    if(![fmdb open]){
        return NO;
    }
    
    NSString *primaryKey = [self getTablePrimaryKey:tableName];
    NSMutableDictionary *dictM = dic.mutableCopy;
    [dictM setValue:primaryKeyValue forKey:primaryKey];
    
    NSString *sqlStr = [self createAddSQL:tableName object:dictM];
    BOOL result = [fmdb executeUpdate:sqlStr];
    [fmdb close];
    
    return result;
}

//生成插入的SQL
- (NSString *) createAddSQL:(NSString *)tableName object:(NSMutableDictionary *)dic
{
    NSMutableString *msKey = [[NSMutableString alloc] initWithString:@"("];
    NSMutableString *msValue = [[NSMutableString alloc] initWithString:@"("];
    NSArray *allKeys = [dic allKeys];
    for (NSString *key in allKeys) {
        if ([dic objectForKey:key] == nil) {
            continue;
        }
        [msKey appendFormat:@"%@,",key];
        [msValue appendFormat:@"'%@',",[dic objectForKey:key]];
    }
    NSString *keys = [NSString stringWithFormat:@"%@)",[msKey substringToIndex:[msKey length] - 1]];
    NSString *values = [NSString stringWithFormat:@"%@)",[msValue substringToIndex:[msValue length] - 1]];
    NSString *sql = [NSString stringWithFormat:@"insert into %@ %@ values %@",tableName,keys,values];
    return sql;
}

//生成修改的SQL
-(NSString *)createUpdateSQL:(NSString *)tableName object:(NSDictionary *)dic AndprimaryKeyValue:(NSString *)primaryKeyValue
{
    NSMutableString *ms = [[NSMutableString alloc] init];
    NSArray *allKeys = [dic allKeys];
    for (NSString *key in allKeys) {
        [ms appendFormat:@"%@='%@',",key,[dic objectForKey:key]];
    }
    
    NSString *primaryKey = [self getTablePrimaryKey:tableName];
    NSString *updateSQL = [NSString stringWithFormat:@"%@",[ms substringToIndex:[ms length] - 1]];
    NSString *sql = [NSString stringWithFormat:@"update %@ set %@ where %@ = '%@'",tableName,updateSQL,primaryKey,primaryKeyValue];
    
    return sql;
}

/**
 更新tableName表中的数据，dic中必须包含primarykey的值
 
 @param dic 字典数据
 @param tableName 表名
 @param primaryKeyValue 主键值
 @return 更新tableName表中的数据
 */
-(BOOL)updateObject:(NSDictionary *)dic tableName:(NSString *)tableName AndprimaryKeyValue:(NSString *)primaryKeyValue
{
    NSString *path = [self getTablePahtFromName:tableName];
    FMDatabase *fmdb = [FMDatabase databaseWithPath:path];
    if(![fmdb open]){
        NSLog(@"数据库打开失败!");
        return NO;
    }
    
    NSString *sqlStr = [self createUpdateSQLWithPrimaryKey:primaryKeyValue tableName:tableName object:dic];
    BOOL result = [fmdb executeUpdate:sqlStr];
    [fmdb close];
    
    return result;
}

/**
 *  生成更新表数据的SQL语句
 *
 *  @param primaryValue 主键值
 *  @param tableName  表名
 *  @param dic        新数据
 *
 *  @return sql语句
 */
- (NSString *)createUpdateSQLWithPrimaryKey:(NSString *)primaryValue tableName:(NSString *)tableName object:(NSDictionary *)dic{
    NSMutableString *ms = [[NSMutableString alloc] init];
    NSArray *allKeys = [dic allKeys];
    for (NSString *key in allKeys) {
        [ms appendFormat:@"%@='%@',",key,[dic objectForKey:key]];
    }
    NSString * primaryKey = [self getTablePrimaryKey:tableName];
    NSString *updateSQL = [NSString stringWithFormat:@"%@",[ms substringToIndex:[ms length] - 1]];
    NSString *sql = [NSString stringWithFormat:@"update %@ set %@ where %@ = '%@'",tableName,updateSQL,primaryKey,primaryValue];
    return sql;
}

/**
 根据主键获取对应的记录
 @param primaryValue 主键值
 @param tableName 表名
 @return 对象
 */
- (NSMutableDictionary *)getObjectWithPrimaryKey:(NSString *)primaryValue tableName:(NSString *)tableName;
{
    NSString *path = [self getTablePahtFromName:tableName];
    NSSLog(@"path=== %@",path);
    FMDatabase *fmdb = [FMDatabase databaseWithPath:path];
    if(![fmdb open]){
        NSLog(@"数据库打开失败!");
        return nil;
    }
    
    NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@ = '%@'",tableName,[self getTablePrimaryKey:tableName],primaryValue];
    FMResultSet *rs = [fmdb executeQuery:sql];
    NSMutableArray *columns = [self getColumnNames:rs];
    NSMutableDictionary *dic = nil;
    if([rs next]){
        dic = [[NSMutableDictionary alloc]init];
        for (NSString *columnName in columns) {
            if ([rs stringForColumn:columnName] == nil) {
                continue;
            }
            [dic setObject:[rs stringForColumn:columnName] forKey:columnName];
        }
    }
    [rs close];
    [fmdb close];
    return dic;
}

//获得表的所有列名
- (NSMutableArray *) getColumnNames:(FMResultSet*) rs{
    NSMutableArray *list = [[NSMutableArray alloc] init];
    for (int index = 0;index < rs.columnCount;index ++){
        [list addObject:[rs columnNameForIndex:index]];
    }
    return list;
}

/**
 自定义删除条件删除对应的数据
 
 @param primaryKeyValue 删除条件 主键值
 @param tableName 表名
 @return 是否成功删除
 */
-(BOOL)deleteObjectWithprimaryKeyValue:(NSString *)primaryKeyValue tableName:(NSString *)tableName
{
    NSString *path = [self getTablePahtFromName:tableName];
    FMDatabase *fmdb = [FMDatabase databaseWithPath:path];
    if(![fmdb open]){
        NSLog(@"数据库打开失败!");
        return NO;
    }
    
    NSString *deleteSql = [NSString stringWithFormat:@"delete from %@ where %@ ='%@'",tableName,[self getTablePrimaryKey:tableName],primaryKeyValue];
    BOOL result = [fmdb executeUpdate:deleteSql];
    [fmdb close];
    return result;
}

#pragma mark - 判断表中是否存在某一元素
-(BOOL)judgeTableIsExistColumn:(NSString *)key AndTable:(NSString *)tableName AndFmdb:(FMDatabase *)fmdb
{
    NSString * sql = [NSString stringWithFormat:@"SELECT %@ FROM %@", key, tableName];
    FMResultSet *rs = [fmdb executeQuery:sql];
    if (rs != nil) {
        return YES;
    }
    return NO;
}

#pragma mark - 获取主键值
-(NSString *)getTablePrimaryKey:(NSString *)tableName
{
    NSString * primaryKey = [NSString stringWithFormat:@"%@_id",tableName];
    return primaryKey;
}

#pragma mark - 判断是否需要创建表
-(BOOL)creatTableIsExist:(NSString *)tableName
{
    NSString * primaryKey = [self getTablePrimaryKey:tableName];
    NSString * tableDBPath = [self getTablePahtFromName:tableName];
    
    if (sqlite3_open([tableDBPath UTF8String],&db) != SQLITE_OK) {
        sqlite3_close(db);
        return NO;
    }else
    {
        char *err;
        NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@ TEXT PRIMARY KEY);", tableName, primaryKey];
        if (sqlite3_exec(db,[createSQL UTF8String],NULL,NULL,&err) != SQLITE_OK) {
            sqlite3_close(db);
            return NO;
        }
    }
    
    return YES;
}

#pragma mark - 获取表路径
-(NSString *)getTablePahtFromName:(NSString *)tableName{
    NSString *documentDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString * path = [documentDirectory stringByAppendingPathComponent:[NSString stringWithFormat:@"%@_table",tableName]];
    return path;
}

-(void)deleteAllTable
{
    NSString *DocumentsPath = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];
    NSDirectoryEnumerator *enumerator = [[NSFileManager defaultManager] enumeratorAtPath:DocumentsPath];
    for (NSString *fileName in enumerator) {
        [[NSFileManager defaultManager] removeItemAtPath:[DocumentsPath stringByAppendingPathComponent:fileName] error:nil];
    }
}

-(long long) folderSizeAtPath3:(NSString*) folderPath{
    return [self folderSizeAtPath:[folderPath cStringUsingEncoding:NSUTF8StringEncoding]];
}

//Private
-(long long)folderSizeAtPath:(const char*)folderPath{
    long long folderSize = 0;
    DIR* dir = opendir(folderPath);
    if (dir == NULL) return 0;
    struct dirent* child;
    while ((child = readdir(dir))!=NULL) {
        if (child->d_type == DT_DIR && (
            (child->d_name[0] == '.' && child->d_name[1] == 0) || // 忽略目录 .
            (child->d_name[0] == '.' && child->d_name[1] == '.' && child->d_name[2] == 0)// 忽略目录 ..
            )) continue;
        NSUInteger folderPathLength = strlen(folderPath);
        char childPath[1024]; // 子文件的路径地址
        stpcpy(childPath, folderPath);
        if (folderPath[folderPathLength-1] != '/'){
            childPath[folderPathLength] = '/';
            folderPathLength++;
        }
        stpcpy(childPath+folderPathLength, child->d_name);
        childPath[folderPathLength + child->d_namlen] = 0;
        if (child->d_type == DT_DIR){ // directory
            folderSize += [self folderSizeAtPath:childPath]; // 递归调用子目录
            // 把目录本身所占的空间也加上
            struct stat st;
            if(lstat(childPath, &st) == 0) folderSize += st.st_size;
        }else if (child->d_type == DT_REG || child->d_type == DT_LNK){ // file or link
            struct stat st;
            if(lstat(childPath, &st) == 0) folderSize += st.st_size;
        }
    }
    return folderSize;
}

@end
